/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package dao;

import entitys.Products;
import java.util.List;
import utils.DBUtils;
import java.sql.*;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 * 商品类的数据库操作类
 * @author Administrator
 */
public class ProductsDAO {
    /**
     * 查询所有的商品
     * @return List<Products>
     */
    public List<Products> queryAllProducts() {
        List<Products> productsList = null;
        String sql = "select productid,productname,productprice,productcount from products";
        Connection conn = DBUtils.getConnection();
        PreparedStatement pst = null;
        try {
            pst = conn.prepareStatement(sql);
            ResultSet rs = pst.executeQuery();
            productsList = new ArrayList<Products>();
            while( rs.next() ) {
                Products products = new Products();
                products.setId(rs.getInt(1));
                products.setName(rs.getString(2));
                products.setPrice(rs.getDouble(3));
                products.setCount(rs.getInt(4));
                productsList.add(products);
            }
        } catch (SQLException ex) {
            Logger.getLogger(ProductsDAO.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            DBUtils.close(conn, pst);
        }
        return productsList;
    }
    
    /**
     * 通过id查询商品的详细信息
     * @param id 商品编号
     * @return 查询到的商品
     */
    public Products queryProductByID( int id ) {
        Products products = null;
        String sql = "select productid,productname,productprice,productcount "
                + "from products where productId=?";
        Connection conn = DBUtils.getConnection();
        PreparedStatement pst = null;
        try {
            pst = conn.prepareStatement(sql);
            pst.setInt(1, id);
            ResultSet rs = pst.executeQuery();
            if( rs.next() ) {
                products = new Products();
                products.setId(rs.getInt(1));
                products.setName(rs.getString(2));
                products.setPrice(rs.getDouble(3));
                products.setCount(rs.getInt(4));
            }
        } catch (SQLException ex) {
            Logger.getLogger(ProductsDAO.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            DBUtils.close(conn, pst);
        }
        return products;
    }
    
    /**
     * 通过ID修改商品的库存量
     * @param id  商品编号
     * @param number  要修改的数量
     * @return 受影响行数
     */
    public Integer modifyProductByID( int id, int number ) {
        int result = 0;
        String sql = "update products set productcount=productcount-? where productid=?";
        Connection conn = DBUtils.getConnection();
        PreparedStatement pst = null;
        try {
            pst = conn.prepareStatement(sql);
            pst.setInt(1, number);
            pst.setInt(2, id);
            result = pst.executeUpdate();
        } catch (SQLException ex) {
            Logger.getLogger(ProductsDAO.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            DBUtils.close(conn, pst);
        }
        return result;
    }
}
